#! /bin/bash

PSQL="psql --username=freecodecamp --dbname=worldcup --no-align --tuples-only -c"

# Do not change code above this line. Use the PSQL variable above to query your database.

echo -e "\nTotal number of goals in all games from winning teams:"
echo "$($PSQL "SELECT SUM(winner_goals) FROM games")"

echo -e "\nTotal number of goals in all games from both teams combined:"
echo "$($PSQL "SELECT SUM(winner_goals + opponent_goals) FROM games")"

echo -e "\nAverage number of goals in all games from the winning teams:"
echo "$($PSQL "SELECT (SUM(winner_goals::DECIMAL) / COUNT(winner_id)) AS result FROM games")"

echo -e "\nAverage number of goals in all games from the winning teams rounded to two decimal places:"
echo "$($PSQL "SELECT ROUND((SUM(winner_goals::DECIMAL) / COUNT(winner_id)), 2) AS result FROM games")"

echo -e "\nAverage number of goals in all games from both teams:"
echo "$($PSQL "SELECT (SUM(winner_goals::DECIMAL + opponent_goals::DECIMAL) / count(*)) FROM games")"

echo -e "\nMost goals scored in a single game by one team:"
echo "$($PSQL "SELECT MAX(winner_goals) FROM games")"

echo -e "\nNumber of games where the winning team scored more than two goals:"
echo "$($PSQL "SELECT COUNT(*) FROM games WHERE winner_goals > 2")"

echo -e "\nWinner of the 2018 tournament team name:"
echo "$($PSQL "SELECT teams.name FROM games LEFT JOIN teams ON games.winner_id = teams.team_id where year = 2018 GROUP BY teams.name ORDER BY COUNT(*) DESC LIMIT 1")"

echo -e "\nList of teams who played in the 2014 'Eighth-Final' round:"
echo "$($PSQL "select name from teams where team_id in (select winner_id from games where year = 2014 and round = 'Eighth-Final' union  select opponent_id from games where year = 2014 and round = 'Eighth-Final') order by name")"

echo -e "\nList of unique winning team names in the whole data set:"
echo "$($PSQL "select DISTINCT(teams.name) from games left join teams on games.winner_id = teams.team_id order by teams.name")"

echo -e "\nYear and team name of all the champions:"
echo "$($PSQL "SELECT year, teams.name FROM games LEFT JOIN teams ON games.winner_id = teams.team_id GROUP BY year, teams.name ORDER BY COUNT(*) DESC, year ASC LIMIT 2")"

echo -e "\nList of teams that start with 'Co':"
echo "$($PSQL "SELECT name FROM teams WHERE name LIKE 'Co%'")"
